-- @owner: cr13
-- @date: 2025/3/27
-- @testpoint: 视图依赖多个自定义函数，删除并重建自定义函数，部分合理报错
--step1:建表;expect:成功
drop table if exists employees;
CREATE TABLE employees (
 employee_id serial PRIMARY KEY,
 employee_name varchar(50) NOT NULL,
 department varchar(50),
 salary numeric(10, 2),
 hire_date date
);
INSERT INTO employees (employee_name, department, salary, hire_date)
VALUES
 ('张三', '研发部', 8000.00, '2020-01-01'),
 ('李四', '市场部', 7500.00, '2021-03-15'),
 ('王五', '财务部', 7000.00, '2019-11-20'),
 ('赵六', '研发部', 8500.00, '2022-05-10');
 --step2:创建2个自定义函数;expect:成功
drop function if exists calculate_working_years;
CREATE FUNCTION calculate_working_years(p_hire_date date) RETURNS integer
AS $$
DECLARE
 v_working_years integer;
 v_current_date date := current_date;
BEGIN
 v_working_years := extract(year from age(v_current_date, p_hire_date));
 RETURN v_working_years;
END;
$$ LANGUAGE plpgsql;
/
drop function if exists calculate_bonus_percentage;
CREATE FUNCTION calculate_bonus_percentage(p_working_years integer) RETURNS numeric(5, 2)
AS $$
DECLARE
 v_bonus_percentage numeric(5, 2);
BEGIN
 IF p_working_years < 3 THEN
 v_bonus_percentage := 0.1;
 ELSIF p_working_years >= 3 AND p_working_years < 5 THEN
 v_bonus_percentage := 0.15;
 ELSE
 v_bonus_percentage := 0.2;
 END IF;
 RETURN v_bonus_percentage;
END;
$$ LANGUAGE plpgsql;
/
--step3:创建视图引用2个自定义函数;expect:成功
drop view if exists employee_bonus_view;
CREATE VIEW employee_bonus_view AS
SELECT employee_name,
 calculate_working_years(hire_date) AS working_years,
 calculate_bonus_percentage(calculate_working_years(hire_date)) AS bonus_percentage
FROM employees;
--step4:删除其中一个自定义函数;expect:成功
drop FUNCTION calculate_working_years;
--step5:查询视图状态;expect:无效
select valid from pg_object where object_oid='employee_bonus_view'::regclass;
--step6:查询视图数据;expect:报错
select * from employee_bonus_view;
--step7:查询视图定义;expect:有告警
select pg_get_viewdef('employee_bonus_view');
--step8:重建函数;expect:成功
CREATE FUNCTION calculate_working_years(p_hire_date date) RETURNS integer
AS $$
DECLARE
 v_working_years integer;
 v_current_date date := current_date;
BEGIN
 v_working_years := extract(year from age(v_current_date, p_hire_date));
 RETURN v_working_years;
END;
$$ LANGUAGE plpgsql;
/
--step9:查询视图定义;expect:目前数据库挂掉
select pg_get_viewdef('employee_bonus_view');
--step10:清理环境;expect:成功
drop table if exists employees;
drop FUNCTION calculate_working_years;
drop FUNCTION calculate_bonus_percentage;
drop view if exists employee_bonus_view;